﻿

CREATE PROCEDURE [dbo].[balcon_GenRegistruCasa2]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@Cod varchar(50),
	@DeLaCont dbo.TipCont,
	@PanaLaCont dbo.TipCont,
	@DataDocJust bit
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF @DataDocJust=1
		SELECT ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,ruld.DataDocJust As DataDoc,
		ct.DebitInitial - ct.CreditInitial + 
		(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj 
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContDebit = ct.Cont) AND rlj.DataDocJust<ruld.DataDocJust )
		-(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj 
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContCredit = ct.Cont) AND rlj.DataDocJust<ruld.DataDocJust ) 
			sodlzi,
		SUM(CASE WHEN ruld.ContDebit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajDebit,
		SUM(CASE WHEN ruld.ContCredit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajCredit
		FROM [Conturi] ct 
		LEFT OUTER JOIN [ConturiSpeciale] cs ON ct.Cont = cs.Cont AND ct.CodUnitate = cs.CodUnitate 
		JOIN [Rulaje] ruld ON ct.CodUnitate=ruld.CodUnitate AND (ruld.ContDebit = ct.Cont OR ruld.ContCredit=ct.Cont)
		JOIN [RulajeDoc] rd ON ruld.IDDocument = rd.IDDocument
		WHERE ct.CodUnitate = @CodUnitate AND rd.DataDoc<=@PanaLaData AND rd.DataDoc>=@DeLaData
		AND (LEN(@Cod)=0 OR cs.Tipuri LIKE @Cod)
		GROUP BY ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,ruld.DataDocJust,ct.Cont
	ELSE
		SELECT ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,
		ct.DebitInitial - ct.CreditInitial + 
		(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContDebit = ct.Cont) AND rjd.DataDoc<rd.DataDoc )
		-(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContCredit = ct.Cont) AND rjd.DataDoc<rd.DataDoc ) 
			sodlzi,
		SUM(CASE WHEN ruld.ContDebit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajDebit,
		SUM(CASE WHEN ruld.ContCredit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajCredit
		FROM [Conturi] ct 
		LEFT OUTER JOIN [ConturiSpeciale] cs ON ct.Cont = cs.Cont AND ct.CodUnitate = cs.CodUnitate 
		JOIN [Rulaje] ruld ON ct.CodUnitate=ruld.CodUnitate AND (ruld.ContDebit = ct.Cont OR ruld.ContCredit=ct.Cont)
		JOIN [RulajeDoc] rd ON ruld.IDDocument = rd.IDDocument
		WHERE ct.CodUnitate = @CodUnitate AND rd.DataDoc<=@PanaLaData AND rd.DataDoc>=@DeLaData
		AND (LEN(@Cod)=0 OR cs.Tipuri LIKE @Cod)
		GROUP BY ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,ct.Cont
	
		
END